﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace DB
{
    /// <summary>
    /// SQLServer2000数据库操作(使用该类时必须先对静态变量SqlConnStr进行赋值，静态变量SqlConnWindows可根据需要赋值)
    /// </summary>
    public sealed class SqlProvider
    {
        #region 静态变量

        /// <summary>
        /// 数据库连接字符串(混合身份登录)
        /// </summary>
        public static string SqlConnStr = "Server=(local);Integrated Security=SSPI;database=master";

        /// <summary>
        /// 数据库连接字符串(windows身份登录)
        /// </summary>
        public static string SqlConnWindows = "Server=(local);Integrated Security=SSPI;database=master";

        #endregion

        #region 和master数据库有关系的方法

        #region 返回集合和数量

        /// <summary>
        /// 获得所有数据库的集合(若返回为null，则连接有错)
        /// </summary>
        /// <returns>DataTable</returns>
        public static DataTable GetDatabases()
        {
            try
            {
                return SqlHelper.ExecuteQuery(SqlConnWindows, CommandType.Text, "select * from sysdatabases", null).Tables[0];
            }
            catch
            {
                return null;
            }
        }

        /// <summary>
        /// 获得所有用户自定义数据库的集合(若返回为null，则连接有错)
        /// </summary>
        /// <returns>DataTable</returns>
        public static DataTable GetUserDatabases()
        {
            try
            {
                return SqlHelper.ExecuteQuery(SqlConnWindows, CommandType.Text, "select * from sysdatabases where name not in('master','model','msdb','Northwind','pubs','tempdb')", null).Tables[0];
            }
            catch
            {
                return null;
            }
        }

        /// <summary>
        /// 获得所有数据库名的集合(若返回为null，则连接有错)
        /// </summary>
        /// <returns>DataTable</returns>
        public static DataTable GetDatabaseNames()
        {
            try
            {
                return SqlHelper.ExecuteQuery(SqlConnWindows, CommandType.Text, "select name from sysdatabases", null).Tables[0];
            }
            catch
            {
                return null;
            }
        }

        /// <summary>
        /// 获得所有用户自定义数据库名的集合(若返回为null，则连接有错)
        /// </summary>
        /// <returns>DataTable</returns>
        public static DataTable GetUserDatabaseNames()
        {
            try
            {
                return SqlHelper.ExecuteQuery(SqlConnWindows, CommandType.Text, "select name from sysdatabases where name not in('master','model','msdb','Northwind','pubs','tempdb')", null).Tables[0];
            }
            catch
            {
                return null;
            }
        }

        /// <summary>
        /// 获得所有数据库的数量(若返回-1，则连接有错)
        /// </summary>
        /// <returns>int</returns>
        public static int GetDatabasesCount()
        {
            DataTable dt = GetDatabaseNames();
            if (dt == null)
            {
                return -1;
            }
            return dt.Rows.Count;
        }

        /// <summary>
        /// 获得用户自定义数据库的数量(若返回-1，则连接有错)
        /// </summary>
        /// <returns>int</returns>
        public static int GetUserDatabasesCount()
        {
            DataTable dt = GetUserDatabaseNames();
            if (dt == null)
            {
                return -1;
            }
            return dt.Rows.Count;
        }

        /// <summary>
        /// 获得数据库数据文件的位置(若返回null，则连接有错)
        /// </summary>
        /// <param name="strDBName">数据库名</param>
        /// <returns>string</returns>
        public static string GetDataFilename(string strDBName)
        {
            try
            {
                int count = SqlHelper.GetCount(SqlConnWindows, "sysdatabases", "name = '" + strDBName.Trim() + "'");
                if (count <= 0)
                {
                    return "数据库 " + strDBName + " 不存在！";
                }
                return GetSysdatabasesRow(strDBName)["filename"].ToString();
            }
            catch
            {
                return null;
            }
        }

        /// <summary>
        /// 判断是否存在数据库(strDBName)(-1:连接错误；0:不存在；1:存在)
        /// </summary>
        /// <param name="strDBName">数据库名</param>
        /// <returns>int</returns>
        public static int IsExistDatabase(string strDBName)
        {
            try
            {
                int count = SqlHelper.GetCount(SqlConnWindows, "sysdatabases", "name = '" + strDBName.Trim() + "'");
                if (count > 0)
                {
                    return 1;
                }
                return 0;
            }
            catch
            {
                return -1;
            }
        }

        #endregion

        #region 分离和附加数据库

        /// <summary>
        /// 分离数据库(-1:连接有错；0:成功；1:失败；2:不存在要分离的数据库)
        /// </summary>
        /// <param name="strDBName">数据库名</param>
        /// <returns>int</returns>
        public static int DetachDB(string strDBName)
        {
            int flag = 0;
            try
            {
                int index = IsExistDatabase(strDBName);
                switch (index)
                { 
                    case -1:
                        return -1;
                    case 0:
                        return 2;
                    case 1:
                        string sql = "exec sp_detach_db '" + strDBName.Trim() + "', 'true'";
                        int count = SqlHelper.ExecuteNonQuery(SqlConnWindows, CommandType.Text, sql, null);
                        if (count == -1)
                        {
                            flag = 0;
                        }
                        else
                        {
                            flag = 1;
                        }
                        break;
                }

            }
            catch
            {
                return -1;
            }
            return flag;
        }

        #endregion

        #endregion

        #region 指定数据库的方法

        /// <summary>
        /// 获得连接数据库的所有用户表的集合
        /// </summary>
        /// <returns>DataTable</returns>
        public static DataTable GetUserTables()
        {
            try
            {
                return SqlHelper.ExecuteQuery(SqlConnStr, CommandType.Text, "select name from sysobjects where xtype = 'U' and status > 0", null).Tables[0];
            }
            catch
            {
                return null;
            }
        }

        /// <summary>
        /// 获得连接数据库的所有用户表的数量(若返回-1，则连接有错)
        /// </summary>
        /// <returns>int</returns>
        public static int GetUserTablesCount()
        {
            DataTable dt = GetUserTables();
            if (dt == null)
            {
                return -1;
            }
            return dt.Rows.Count;
        }

        /// <summary>
        /// 获得数据表的字段的集合(若返回为null，则连接有错)
        /// </summary>
        /// <param name="strTableName">表名</param>
        /// <returns>DataTable</returns>
        public static DataTable GetTableColumns(string strTableName)
        {
            try
            {
                string sql = "select name from syscolumns where id = (select id from sysobjects where name = '" + strTableName.Trim() + "' and xtype = 'U') order by colid";
                return SqlHelper.ExecuteQuery(SqlConnStr, CommandType.Text, sql, null).Tables[0];
            }
            catch
            {
                return null;
            }
        }

        /// <summary>
        /// 获得数据表的字段的数量(若返回-1，则连接有错)
        /// </summary>
        /// <param name="strTableName">表名</param>
        /// <returns>int</returns>
        public static int GetTableColumnsCount(string strTableName)
        {
            DataTable dt = GetTableColumns(strTableName);
            if (dt == null)
            {
                return -1;
            }
            return dt.Rows.Count;
        }

        /// <summary>
        /// 获得数据表中字段的集合(包括字段名name，字段类型type，字段长度length)(若返回null，则连接有错)
        /// </summary>
        /// <param name="strTableName">表名</param>
        /// <returns>DataTable</returns>
        public static DataTable GetColumns(string strTableName)
        {
            string sql = "select A.name as name,B.name as type,A.length as length from syscolumns A,systypes B where A.xusertype = B.xusertype and A.id = (select id from sysobjects where name = '" + strTableName.Trim() + "' and xtype = 'U')  order by A.colid";
            try
            {
                return SqlHelper.ExecuteQuery(SqlConnStr, CommandType.Text, sql, null).Tables[0];
            }
            catch
            {
                return null;
            }
        }

        #endregion

        #region 私有方法

        /// <summary>
        /// 从sysdatabases表中得到一条数据(按数据库名查询，若返回null，则连接有错)
        /// </summary>
        /// <param name="strDBName">数据库名</param>
        /// <returns>DataRow</returns>
        private static DataRow GetSysdatabasesRow(string strDBName)
        {
            try
            {
                return SqlHelper.ExecuteQuery(SqlConnWindows, CommandType.Text, "select * from sysdatabases where name = '" + strDBName.Trim() + "'", null).Tables[0].Rows[0];
            }
            catch
            {
                return null;
            }
        }

        #endregion
    }
}
